Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Packages, Procedures, and Functions

Another way to improve performance of your SQL statements is by using packages, procedures, and functions. Packages can help improve performance by storing together procedures and functions that are often used together. By storing these elements together, you can reduce the I/O required to bring them into memory from disk. Because these elements are often used together, they can also be loaded from disk together.

By using stored procedures, you benefit in several ways. Stored procedures allow you to reduce the amount of data sent across the network. The stored procedure requires fewer instructions to be sent to the server; in many cases, less data must be sent back to the client from the server.

A second benefit of a stored procedure is the increased chance that the SQL statement can be used by other processes. Because the SQL statement is defined and used by many processes, chances are good that the SQL statement will already be parsed in the shared SQL area and available to other users.

Chapter 28, “Using Procedures, Functions, and Packages,” covers how to use these elements to improve the performance of SQL statements.

Optimization Approaches

Oracle offers several options for optimization techniques. Among these are a cost-based approach and a rule-based approach. The approach you take depends both on your application and your data. In most cases, the cost-based approach is recommended because it determines an execution plan that is as good or better than the rule-based approach.

The following sections look at the optimization approaches available from Oracle and when each approach is appropriate. Remember that you can use hints to specifically tell Oracle how you want the SQL statement to be executed. There are several ways you can indicate your preference, as described later in this chapter.

The discussion here is limited to an overview because the Oracle optimizer is detailed in Chapter 27, “Using the Oracle Optimizer.”

Rule-Based Approach

The rule-based approach to Oracle optimization is straightforward and consistent. In the rule-based approach, the execution plan is derived by examining the available paths and ranking them against a list of predetermined values for these paths (see Figure 26.2).


Figure 26.2  The rule-based optimization rankings.

With the rule-based optimization approach, the optimizer determines the ways to execute the SQL statement. If there is more than one way to execute the SQL statement, the table in Figure 26.2 is used to choose the approach with the lowest ranking.

Cost-Based Approach

The cost-based approach to optimization uses existing knowledge of the database to choose the most efficient execution plan. During the normal operation of the RDBMS, statistics are gathered on the data distribution and storage characteristics. The optimizer uses this information to determine the most optimal execution plan.

This optimization approach takes three steps:

1.  The optimizer generates a set of possible execution plans, just as it does with the rule-based optimization approach.
2.  The cost of each plan is determined based on statistics gathered about the database. This cost is based on CPU time and the I/O and memory necessary to execute the plan.
3.  The optimizer compares the costs and chooses the execution plan with the lowest cost.

The cost-based approach is usually preferred. In some cases, the rule-based approach may be more appropriate, as discussed in Chapter 27, “Using the Oracle Optimizer.”

Hints

You can use hints to inform the optimizer of some special facts you know about the data or the SQL statement that may affect the execution plan. By using hints, you indicate that the SQL statement may be more efficient by using a certain execution plan (for example, a full-table scan or increased parallelism). Hints are detailed in Chapter 30, “Using Hints.”

Review of Optimization Approaches

Oracle offers different optimization approaches. Among these are a cost-based approach and a rule-based approach. The approach you take depends both on your application and your data. In general, the cost-based approach is the recommended approach. In most cases, the cost-based approach determines an execution plan that is as good or better than the rule-based approach.

The optimization approaches and the use of hints are discussed in later chapters. By properly optimizing your system, you can achieve the most efficient execution of your SQL statements.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.